<?php
	require("frmCookie.php");
	
	$iOpcion = $_GET['iOpcion'];
	$esOriginal = $_GET['esOriginal'];
	
?>
<html>
	<head>
    	<?php
		require("frmHeader.php");
		require("functions.php");
		?>
		<script language="javascript" src="scripts/ajax.js"></script>
		<script language="javascript" src="scripts/validar.js"></script>
		<script language="javascript"></script>
	</head>
	<body>
    	<table cellpadding="0" cellspacing="0" align="center" class="tablaPrincipal">
        	<tr>
            	<td align="center">
                	<?php
					require("frmSession.php"); 
					
					//Redirecciona a SINPERMISOS.PHP en caso de no tener permisos
					
					if (!(in_array(48000, $_SESSION[permisos])))
					{
					?>
					  <script language="JavaScript" type="text/javascript">
					  <!--
					  window.location.href = "sinPermisos.php"
					  //-->
					  </script>
					<?php
					}
					$titulo = "Reporte Motorola";
					
					?>
                </td>
            </tr>
            <tr>
            	<td align="center">
                	<?php
					require("frmTitulo.php");
					?>
                </td>
            </tr>
            <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
            	<td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="menuPrincipal">
                    	<tr>
                        	<td>
								<?php
                                require("frmMenu.php");
                                ?>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
            <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="tituloPantalla">
                    	<tr>
                        	<td align="center">
                            	<p class="texto" align="left"><?php echo $titulo; ?></p>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
             <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="estadoPantalla">
                    	<tr>
                        	<td align="center">
                            	<p class="texto" align="left"><?php echo $mensaje; ?></p>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
             <tr>
            	<td height="20">&nbsp;
                	
                </td>
            </tr>
            <tr>
                <td align="center">
                	<table cellpadding="0" cellspacing="0" align="center" class="contenedorPrincipal">
                    	<tr>
                        	<td align="">
                        		<?php
                        		switch($iOpcion)
                        		{
                        			case 1:
                        			{
                				?>
                				<form name="frm" action="excelMotorola.php?iOpcion=2&esOriginal=<?php echo $esOriginal;?>" onSubmit="return validaForm();" method="post">
                					<table>
                						<tr>
                							<td>Fecha Desde: </td>
                							<td><input type="text" name="txtFechaDesde" size="10" maxlength="10" /></td>
                						</tr>
                						<tr>
                							<td>Fecha Hasta: </td>
                							<td><input type="text" name="txtFechaHasta" size="10" maxlength="10" /></td>
                						</tr>
                						<tr>
                							<td colspan="2" align="center"><input type="submit" name="cmdEnviar" value="Generar" /></td>
                						</tr>
                					</table>
                				</form>
                        		<?php
                        				break;
                        			}
                        			case 2:
                        			{
                        		
		                        		list($dia, $mes, $ano) = split("/", $_POST[txtFechaDesde]);
										$fechaDesde = $ano . "-" . $mes . "-" . $dia;
		                				
		                				list($dia, $mes, $ano) = split("/", $_POST[txtFechaHasta]);
										$fechaHasta = $ano . "-" . $mes . "-" . $dia;

										//Borra los msn que tengan menos de 10 caracteres
										$mySQL = "update ordenesmotorola set msn = '' where length(msn)<10";
										executeSQL($mySQL);
										
										//Trae las ordenes motorola
										$mySQLOrden = "Select s.nombre as sucursal, ec.nombre as estadocobro, o.observacionestadocobro, o.apellido, s.ciudad, s.provincia, o.nroLinea, o.idOrden, o.idOperador, o.imeiSalida, mo.descripcion, mo.nombreFabricante, om.msn, o.imei, o.imeisalida, o.fechaHoraFin, o.fechaActivacionLinea, sxm.codigo as codigoSintoma, u.idMotorola, concat(u.apellido,' ',u.nombre) as usuario, fxm.codigo as codigoFalla, axm.codigo as codigoAccion, s.idSucursal from sucursales s join ordenes o on s.idSucursal = o.idSucursal left join usuarios u on o.idUsuario = u.idUsuario join ordenesmotorola om on o.idOrden = om.idOrden join modelos mo on o.idModelo = mo.idModelo join marcas ma on mo.idMarca = ma.idMarca join reparaciones r on o.idOrden = r.idOrden join sintomasxmarca sxm on r.idSintoma = sxm.idSintoma join fallasxmarca fxm on r.idFalla = fxm.idFalla join accionesxmarca axm on r.idAccion = axm.idAccion left join estadoscobro ec on o.idestadocobro = ec.idestadocobro where o.garantiaservice = 0 and ma.nombre = 'Motorola' and sxm.idMarca = 6 and fxm.idMarca = 6 and axm.idMarca = 6 and o.fechaHoraFin between '" . $fechaDesde . " 00:00:00' and '" . $fechaHasta . " 23:59:59' and o.idEstado in(select idEstado from estados e where e.nombre = 'Reparado') group by om.idOrdenMotorola";
										
										//echo($mySQLOrden);

										$datosParaElExcel = array();
										
		                        		$resultOrden = readSQL("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;".$mySQLOrden);
										
										foreach($resultOrden as $valueOrden)
										{
											//El el array que tiene las filas del Excel
											$filaParaElExcel = array();
											
											$sucursal = $valueOrden['sucursal'];
											$estadoCobro = $valueOrden['estadocobro'];
											$observacionEstadoCobro = $valueOrden['observacionestadocobro'];
											$usuario = $valueOrden['usuario'];
											$recordIdentifier = "CLM";
											$consumerTitle="";
											$consumerFirstName = "NA";
											$consumerSurname = $valueOrden['apellido'];
											$consumerAddress1 = "NA";
											$consumerAddress2 = "NA";
											$consumerAddress3 = "";
											$consumerAddress4 = "";
											$consumerAddress5 = $valueOrden['ciudad'];
											$consumerAddress6 = $valueOrden['provincia'];
											$consumerAddress7 = "";
											$consumerAddress8 = "Argentina";
											$consumerPhoneNumber = $valueOrden['nroLinea'];
											$consumerEmailAddress = "";
											$fieldBulletinNumber = "";
											$consumerGendre = "";
											$outgoingCourierCode = "WI";
											$countryCode = "AR";
											$courierTrackingNumberIn = "WI";
											$courierTrackingNumberOut = "WI";
											$warrantyClaim = "CL".$valueOrden['idOrden'];
											$customerReferenceNumber = $valueOrden['idOrden'];
											switch($valueOrden['idOperador'])
											{
												case 1:
												{
													$airtimeCarrierCode = "MOP";
													break;	
												}
												case 2:
												{
													$airtimeCarrierCode = "CTI";
													break;	
												}
												case 3:
												{
													$airtimeCarrierCode = "PER";
													break;	
												}
												default:
												{
													$airtimeCarrierCode = "000";
													break;	
												}
											}
											
											if($valueOrden['imeiSalida']=='')
											{
												$transactionCode = "REP";
											}
											else
											{
												$transactionCode = "BSW";
											}
											
											$productCode = $valueOrden['descripcion'];
											$transceiverCode = $valueOrden['nombreFabricante'];
											$modelCode = "";
											$factoryCode = "";
											$msnIn = $valueOrden['msn'];
											if($msnIn == '')
											{
												$msnIn = "No Legible - A Corregir";	
											}
											$msnOut = $valueOrden['msn'];
											$imeiIn = $valueOrden['imei'];
											if($valueOrden['imeisalida']=="")
											{
												$imeiOut = $valueOrden['imei'];
											}
											else
											{
												$imeiOut = $valueOrden['imeisalida'];
											}
											$reserved1 = "";
											$reserved2 = "";
											$reserved3 = "";
											$repairStatus = "SHP";
											$partsNotAvailable1 = "";
											$partsNotAvailable2 = "";
											//Es la fecha real
											$repairDate = substr($valueOrden['fechaHoraFin'],8,2) . "/" . substr($valueOrden['fechaHoraFin'],5,2) . "/" . substr($valueOrden['fechaHoraFin'],0,4);
											//Es la hora real
											$repairTime = substr($valueOrden['fechaHoraFin'],11,2) . ":" . substr($valueOrden['fechaHoraFin'],14,2);
											
											$dateShipped = substr($valueOrden['fechaHoraFin'],8,2) . "/" . substr($valueOrden['fechaHoraFin'],5,2) . "/" . substr($valueOrden['fechaHoraFin'],0,4);
											$timeShipped = sumarTiempoRandom($repairTime);
											$dateReceived = substr($valueOrden['fechaHoraFin'],8,2) . "/" . substr($valueOrden['fechaHoraFin'],5,2) . "/" . substr($valueOrden['fechaHoraFin'],0,4);
											$timeReceived = restarTiempoRandom($repairTime);
											$expectedShipDate = "";
											$expectedShipTime = "";
											$repairCycleTime = "00:".tiempoTranscurrido($repairTime, $timeReceived);
											$consumerCycleTime = "00:".tiempoTranscurrido($timeShipped, $timeReceived);
											if(garantiaDeFabricacion($msnIn,substr($valueOrden['fechaHoraFin'],8,2) . "/" . substr($valueOrden['fechaHoraFin'],5,2) . "/" . substr($valueOrden['fechaHoraFin'],0,4)))
											{
												$popWarrantyClaim = "N";	
											}
											else
											{
												$popWarrantyClaim = "Y";	
											}
											if($popWarrantyClaim == "Y")
											{
												$dateOfPurchase = substr($valueOrden['fechaActivacionLinea'],8,2) . "/" . substr($valueOrden['fechaActivacionLinea'],5,2) . "/" . substr($valueOrden['fechaActivacionLinea'],0,4);
											}
											else
											{
												$dateOfPurchase = "";
											}
											$esncsnIn = "";
											$esncsnOut = "";
											$swIn = "NA";
											$swOut = "NA";
											$customerComplaintCode = $valueOrden['codigoSintoma'];
											$technicianID = $valueOrden['idMotorola'];//$valueOrden['apellido'] . ", " . $valueOrden['nombre'];
											$primaryProblemFoundCode = $valueOrden['codigoFalla'];
											$secondaryProblemFoundCode = "";
											$primaryRepairCode = $valueOrden['codigoAccion'];
											$secondaryRepairCode = "";
											$originatingShopID = "";
											$airtime = "NA";
											$reserved1 = "";
											$specialProjectNumbre = "";
											$billingAccountNumbre = "";
											$reserved2 = "";
											$reserved3 = "";
											$reserved4 = "";
											$accesoryDateCode = "";
											$mobileNumberMin = "NA";
											$rmaNumber = "";
											$reserved5 = "";
											$reserved6 = "";
											switch($valueOrden['idSucursal'])
											{
												case 15:
												{
													//Palermo
													$claimMascCode = "AR000112";
													break;	
												}
												case 16:
												{
													//Ramos
													$claimMascCode = "AR000114";
													break;	
												}
												case 17:
												{
													//Moron
													$claimMascCode = "AR000179";
													break;	
												}
												case 18:
												{
													//Viamonte
													$claimMascCode = "AR000115";
													break;	
												}
											}
											$reserved7 = "";
											$reserved8 = "";
											$reserved9 = "";
											$reserved10 = "";
											$reserved11 = "";
											$reserved12 = "";
											$reserved13 = "";
											$reserved14 = "";
											$reserved15 = "";
											
											array_push($filaParaElExcel,$sucursal);
											array_push($filaParaElExcel,$estadoCobro);
											array_push($filaParaElExcel,$observacionEstadoCobro);
											array_push($filaParaElExcel,$usuario);
											array_push($filaParaElExcel,$recordIdentifier);
											array_push($filaParaElExcel,$consumerTitle);
											array_push($filaParaElExcel,$consumerFirstName);
											array_push($filaParaElExcel,$consumerSurname);
											array_push($filaParaElExcel,$consumerAddress1);
											array_push($filaParaElExcel,$consumerAddress2);
											array_push($filaParaElExcel,$consumerAddress3);
											array_push($filaParaElExcel,$consumerAddress4);
											array_push($filaParaElExcel,$consumerAddress5);
											array_push($filaParaElExcel,$consumerAddress6);
											array_push($filaParaElExcel,$consumerAddress7);
											array_push($filaParaElExcel,$consumerAddress8);
											array_push($filaParaElExcel,$consumerPhoneNumber);
											array_push($filaParaElExcel,$consumerEmailAddress);
											array_push($filaParaElExcel,$fieldBulletinNumber);
											array_push($filaParaElExcel,$consumerGendre);
											array_push($filaParaElExcel,$outgoingCourierCode);
											array_push($filaParaElExcel,$countryCode);
											array_push($filaParaElExcel,$courierTrackingNumberIn);
											array_push($filaParaElExcel,$courierTrackingNumberOut);
											array_push($filaParaElExcel,$warrantyClaim);
											array_push($filaParaElExcel,$customerReferenceNumber);
											array_push($filaParaElExcel,$airtimeCarrierCode);
											array_push($filaParaElExcel,$transactionCode);
											array_push($filaParaElExcel,$productCode);
											array_push($filaParaElExcel,$transceiverCode);
											array_push($filaParaElExcel,$modelCode);
											array_push($filaParaElExcel,$factoryCode);
											array_push($filaParaElExcel,$msnIn);
											array_push($filaParaElExcel,$msnOut);
											array_push($filaParaElExcel,$imeiIn);
											array_push($filaParaElExcel,$imeiOut);
											array_push($filaParaElExcel,$reserved1);
											array_push($filaParaElExcel,$reserved2);
											array_push($filaParaElExcel,$reserved3);
											array_push($filaParaElExcel,$repairStatus);
											array_push($filaParaElExcel,$partsNotAvailable1);
											array_push($filaParaElExcel,$partsNotAvailable2);
											array_push($filaParaElExcel,$dateReceived);
											array_push($filaParaElExcel,$timeReceived);
											array_push($filaParaElExcel,$expectedShipDate);
											array_push($filaParaElExcel,$expectedShipTime);
											array_push($filaParaElExcel,$dateShipped);
											array_push($filaParaElExcel,$timeShipped);
											array_push($filaParaElExcel,$repairDate);
											array_push($filaParaElExcel,$repairTime);
											array_push($filaParaElExcel,$repairCycleTime);
											array_push($filaParaElExcel,$consumerCycleTime);
											array_push($filaParaElExcel,$popWarrantyClaim);
											array_push($filaParaElExcel,$dateOfPurchase);
											array_push($filaParaElExcel,$esncsnIn);
											array_push($filaParaElExcel,$esncsnOut);
											array_push($filaParaElExcel,$swIn);
											array_push($filaParaElExcel,$swOut);
											array_push($filaParaElExcel,$customerComplaintCode);
											array_push($filaParaElExcel,$technicianID);
											array_push($filaParaElExcel,$primaryProblemFoundCode);
											array_push($filaParaElExcel,$secondaryProblemFoundCode);
											array_push($filaParaElExcel,$primaryRepairCode);
											array_push($filaParaElExcel,$secondaryRepairCode);
											array_push($filaParaElExcel,$originatingShopID);
											array_push($filaParaElExcel,$airtime);
											array_push($filaParaElExcel,$reserved1);
											array_push($filaParaElExcel,$specialProjectNumbre);
											array_push($filaParaElExcel,$billingAccountNumbre);
											array_push($filaParaElExcel,$reserved2);
											array_push($filaParaElExcel,$reserved3);
											array_push($filaParaElExcel,$reserved4);
											array_push($filaParaElExcel,$accesoryDateCode);
											array_push($filaParaElExcel,$mobileNumberMin);
											array_push($filaParaElExcel,$rmaNumber);
											array_push($filaParaElExcel,$reserved5);
											array_push($filaParaElExcel,$reserved6);
											array_push($filaParaElExcel,$claimMascCode);
											array_push($filaParaElExcel,$reserved7);
											array_push($filaParaElExcel,$reserved8);
											array_push($filaParaElExcel,$reserved9);
											array_push($filaParaElExcel,$reserved10);
											array_push($filaParaElExcel,$reserved11);
											array_push($filaParaElExcel,$reserved12);
											array_push($filaParaElExcel,$reserved13);
											array_push($filaParaElExcel,$reserved14);
											array_push($filaParaElExcel,$reserved15);
											
											if($esOriginal == "true")
											{
												array_push($datosParaElExcel,$filaParaElExcel);
											}

											$mySQLReparacion = "Select p.codigoFabricante, r.cantidad, tdp.codigoMotorola, p.idTipoParte, tpxm.codigo as tipodeparte from reparaciones r join ordenesmotorola om on r.idOrden = om.idOrden join partes p on p.idParte = r.idParte join tipospartes tdp on p.idTipoParte = tdp.idTipoParte join tipospartesxmarca tpxm on p.idtipoparte = tpxm.idtipoparte where om.idOrden = ".$valueOrden['idOrden'] . " and p.codigoTango <> 111111111111 and tpxm.idmarca = 6";
											$resultReparacion = readSQL($mySQLReparacion);
											
											foreach($resultReparacion as $valueReparacion)
											{
												//Si no es ni placa ni equipo
												if($valueReparacion['idTipoParte'] != 3 && $valueReparacion['idTipoParte'] != 4)
												{
													if($esOriginal == "true")
													{
														$filaParaElExcel = array();
													}
													
													$recordIdentifier = "CMP";
													$mascCode = "AR000003";
													$warrantyClaimNumber = "CL".$valueOrden['idOrden'];
													$partReplaced = $valueReparacion['codigoFabricante'];
													$quantityReplaced = $valueReparacion['cantidad'];
													$quantityExchanged = "0";
													$repairOrRefurbish = "R";
													$referenceDesignator = $valueReparacion['tipodeparte'];
													$referenceDesignatorNumber = "";
													$partFailureCode = "";
													
													if($quantityReplaced == 0)
													{
														$resolderorReplace = "RSL"; /*Pregutar*/
													}
													else
													{
														$resolderorReplace = "RPC";
													}
													$reserved1="";
													$reserved2="";
													$reserved3="";
													$reserved4="";
													$reserved5="";
													
													array_push($filaParaElExcel,'');
													array_push($filaParaElExcel,'');
													array_push($filaParaElExcel,'');
													array_push($filaParaElExcel,'');
													array_push($filaParaElExcel,$recordIdentifier);
													array_push($filaParaElExcel,$mascCode);
													array_push($filaParaElExcel,$warrantyClaimNumber);
													array_push($filaParaElExcel,$partReplaced);
													array_push($filaParaElExcel,$quantityReplaced);
													array_push($filaParaElExcel,$quantityExchanged);
													array_push($filaParaElExcel,$repairOrRefurbish);
													array_push($filaParaElExcel,$referenceDesignator);
													array_push($filaParaElExcel,$referenceDesignatorNumber);
													array_push($filaParaElExcel,$partFailureCode);
													array_push($filaParaElExcel,$resolderorReplace);
													array_push($filaParaElExcel,$reserved1);
													array_push($filaParaElExcel,$reserved2);
													array_push($filaParaElExcel,$reserved3);
													array_push($filaParaElExcel,$reserved4);
													array_push($filaParaElExcel,$reserved5);
													
													if($esOriginal == "true")
													{
														array_push($datosParaElExcel,$filaParaElExcel);
													}
												}
											}
											
											if($esOriginal == "false")
											{
												array_push($datosParaElExcel,$filaParaElExcel);
											}
										}
		                        		//echo($fede);
		                        		
		                        		// include package
										include 'Spreadsheet/Excel/Writer.php';
										
										// Fecha Hora Inicio
										$fecha = getdate();
										$dia = $fecha[mday];
										$mes = $fecha[mon];
										$ano = $fecha[year];
										$hora = $fecha[hours];
										$minutos = $fecha[minutes];
										$segundos = $fecha[seconds];
										$archivo =  $ano . "-" . $mes . "-" . $dia . "-" . $hora . "-" . $minutos . "-" . $segundos . "-Motorola.xls";
										
										// create empty file
										if($_SERVER['SERVER_NAME'] == "localhost")
										{
											$host = "/Users/fede/Sites/galander/informes/";
										}
										else
										{
											$host = "/var/www/html/galander/informes/";	
										}
										$excel = new Spreadsheet_Excel_Writer($host . $archivo);
										
										// add worksheet
										$sheet =& $excel->addWorksheet('Hoja 1');
										
										// create format for header row 
										// bold, red with black lower border
										/*$firstRow =& $excel->addFormat();
										$firstRow->setBold();
										$firstRow->setColor('black');
										$firstRow->setBottom(1);*/
										//$firstRow->setBottomColor('black');
										
										// create format for header column
										// blue with black right border
										/*$firstCol =& $excel->addFormat();
										$firstRow->setBold();
										$firstCol->setColor('black');*/
										//$firstCol->setRight(1);
										//$firstCol->setRightColor('black');
										
										// create format for top left corner
										/*$firstRowCol =& $excel->addFormat();
										$firstRowCol->setBottom(1);*/
										//$firstRowCol->setRight(1);
										//$firstRowCol->setBorderColor('black');
										
										// create format for numeric cells
										$text =& $excel->addFormat();
										$text->setScript(1);
										
										$num =& $excel->addFormat();
										$num->setNumFormat('#');
										
										$imeiF =& $excel->addFormat();
										$imeiF->setNumFormat('000000000000000');
										
										$prodcode =& $excel->addFormat();
										$prodcode->setNumFormat('0000000');
										
										$fecIng =& $excel->addFormat();
										$fecIng->setNumFormat('mm-yyyy-dd');
										
										// create format for averaged values
										/*$avg =& $excel->addFormat();
										$avg->setNumFormat('0.00');*/
										
										// add data to worksheet
										$rowCount=0;
										foreach ($datosParaElExcel as $row) 
										{
										  for($colCount=0; $colCount<sizeof($row); $colCount++) 
										  {

											$sheet->writeString($rowCount, $colCount, $row[$colCount]);  
										  }  
										  // get cell coordinates
										  $start = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, 1);
										  	$end = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, (sizeof($row)-1));
											$rowCount++;
										}
										
										// save file to disk
										if ($excel->close() === true) {
											echo "<a class='linknuevo' href='informes/" . $archivo . "'>Descargar Archivo</a>";
										  //echo 'Spreadsheet successfully saved!';  
										} else {
										  echo 'ERROR: Could not save spreadsheet.';
										}
										
										/*foreach($data as $val)
										{
											foreach($val as $val1)
											{
												echo $val1 . "<br>";
											}
										}*/
										break;
									}
                        		}
                        		?>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
	</body>
</html>